﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using fqlmoney.Model;
using MySql.Data.MySqlClient;

namespace fqlmoney.DAL
{
    public class MoneyTypeDAL
    {
        
        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="extsql">sql条件</param>
        /// <param name="order">排序</param>
        /// <param name="pageIndex">页数</param>
        /// <param name="pageCount">条数</param>
        /// <param name="totalPages">总页数</param>
        /// <param name="totalItems">总条数</param>
        /// <returns></returns>
        public List<user_moneytype> GetPage(string extsql, string order, int pageIndex, int pageCount, ref int totalPages, ref int totalItems)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(" SELECT * FROM user_moneytype ");
            sql.Append(" WHERE `t_delete`=0 ");
            sql.Append(extsql);
            if (!string.IsNullOrEmpty(order))
            {
                sql.Append(" order by " + order);
            }
            sql.Append(" limit " + ((pageIndex - 1) * pageCount) + "," + pageCount + ";");
            sql.Append("SELECT COUNT(*) FROM user_moneytype  WHERE `t_delete`=0 " + extsql + ";");
            return DBUtility.MySqlHelper.ExecuteDataReaderPage<user_moneytype>(sql.ToString(), pageCount, ref totalPages, ref totalItems);
        }

        /// <summary>
        /// 获取所有分类
        /// </summary>
        /// <param name="uid">用户id</param>
        /// <returns></returns>
        public List<user_moneytype> GetList(int uid)
        {
            return DBUtility.MySqlHelper.ExecuteDataReader<user_moneytype>("SELECT * FROM `user_moneytype` WHERE t_delete=0 AND u_id=@uid",new MySqlParameter("@uid", uid) );
        }

        /// <summary>
        /// 添加分类
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(user_moneytype model)
        {
            MySqlParameter[] para = {
                new MySqlParameter("@u_id",model.u_id),
                new MySqlParameter("@t_name",model.t_name),
                new MySqlParameter("@t_state",model.t_state)
            };
            return DBUtility.MySqlHelper.ExecuteNonQuery("INSERT INTO `user_moneytype`(`u_id`,`t_name`,`t_state`,`t_delete`) VALUE(@u_id,@t_name,@t_state,0)",para);
        }

        /// <summary>
        /// 更新分类
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(user_moneytype model)
        {
            MySqlParameter[] para = {
                new MySqlParameter("@t_id",model.t_id),
                new MySqlParameter("@t_name",model.t_name),
                new MySqlParameter("@t_state",model.t_state)
            };
            return DBUtility.MySqlHelper.ExecuteNonQuery("UPDATE user_moneytype SET `t_name`=@t_name,`t_state`=@t_state WHERE `t_id`=@t_id", para);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <param name="uid"></param>
        /// <returns></returns>
        public int Delete(int id,int uid)
        {
            MySqlParameter[] para = {
                new MySqlParameter("@id",id),
                new MySqlParameter("@uid",uid)
            };
            return DBUtility.MySqlHelper.ExecuteNonQuery("UPDATE user_moneytype SET `t_delete`=1 WHERE u_id=@uid and `t_id`=@id", para);
        }

    }
}
